/*

 Program collapsetocells.do collapses the IMSS and ENEU data to a
 single dataset of cell-level variables.

 Kumler, Verhoogen, Frias "Enlisting Employees ..." REStat forthcoming

*/



***************** housekeeping ******************;

#delimit;
set more off;

do ${code}housekeeping.do;

tempfile d1 d2;

* parameters;
local fyr = 1988;
local lyr = 2003;
local fqtr = 1;
local lqtr = 4;
local fage_cat2 = 1;
local lage_cat2 = 5;



*****************************************************************************;
************* collapse to cells *********************************************;
*****************************************************************************;

 forvalues spec = 1/4
 {;

     di "spec = `spec'";

  if `spec'==1 {;
             local imss_bylist = "year qtr male age_cat2 metro_area";
             local eneu_bylist = "year male age_cat2 metro_area";
             local filename = "cellpqymetro_age_cat2_`fyr'`lyr'";
           };

  if `spec'==2 {;
             local imss_bylist =  "year qtr male metro_area firmsize3 sector age_cat2";
             local eneu_bylist =  "year male metro_area firmsize3 sector age_cat2";
             local filename = "cellpqymetrofssec_age_cat2_`fyr'`lyr'";
           };

  if `spec'==3 {;
             local imss_bylist =  "year qtr male age_cat2";
             local eneu_bylist =  "year qtr male age_cat2";
             local filename = "cellpqn_age_cat2_`fyr'`lyr'";
           };
  if `spec'==4 {;
             local imss_bylist = "year qtr male age_cat2 metro_area";
             local eneu_bylist = "year qtr male age_cat2 metro_area";
             local filename = "cellpqnmetro_age_cat2_`fyr'`lyr'";
           };



  ************ IMSS ********************;

  ** loop through cells and collapse **;

  forval yr=`fyr'/`lyr'
  {;
   forval qtr=`fqtr'/`lqtr'
   {;
    local month=`qtr'*3;
    di "year = `yr', quarter = `qtr', month = `month'";
    clear;
    use ${work}imss_forhh_`yr'`month';

    su age_cat2;
    local agecatvarmax = r(max);
    local agecatvarmin = r(min);

    forvalues i = `agecatvarmin'/`agecatvarmax'
    {;
     gen age_cat2_imss_`i' = 1 if age_cat2==`i';
     replace age_cat2_imss_`i' = 0 if age_cat2~=`i' & age_cat2~=.;
   };
  forvalues j = 1/5
  {;
     gen firmsize3_imss_`j' = 1 if firmsize3==`j';
     replace firmsize3_imss_`j' = 0 if firmsize3~=`j' & firmsize3~=.;
   };

    **** collapse IMSS data to cell level ****;

    sort `imss_bylist';
    collapse (mean) imss_lt105mw imss_lt110mw rsal7_imss_mn=rsal7 rsal7net_imss_mn=rsal7net
        age_mn_imss=age age_cat2_imss_* firmsize3_imss_* empl_mn_imss = empl_imss
        (median) rsal7_imss_med=rsal7  rsal7net_imss_med=rsal7net age_med_imss=age empl_med_imss = empl_imss
        (sum) empl_imss = imss, by(`imss_bylist');

    tempfile imss_`yr'_`qtr';
    save `imss_`yr'_`qtr'', replace;

  };
 };


  ** stack collapsed cell-level tempfiles **;

  clear;
  set obs 0;
  gen year = .;

  forval yr=`fyr'/`lyr'
  {;
   forval qtr=`fqtr'/`lqtr'
   {;
    append using `imss_`yr'_`qtr'';
  };
 };

  save ${tmp}imss_`filename', replace;

  *************** ENEU ************************;

  clear;
  use ${work}eneu_forimss_19882003;

  keep if year>=`fyr' & year<=`lyr';
  keep if year<2003 | (year==2003 & qtr<=2);

    su age_cat2;
    local agecatvarmax = r(max);
    local agecatvarmin = r(min);

    forvalues i = `agecatvarmin'/`agecatvarmax'
  {;
     gen age_cat2_eneu_`i' = 1 if age_cat2==`i';
     replace age_cat2_eneu_`i' = 0 if age_cat2~=`i' & age_cat2~=.;
   };
  forvalues j = 1/5
  {;
     gen firmsize3_eneu_`j' = 1 if firmsize3==`j';
     replace firmsize3_eneu_`j' = 0 if firmsize3~=`j' & firmsize3~=.;
   };

  ** full-time with imss;

  preserve;
  keep if work_ft_imss==1;

  gen n_eneu_ft_imss = _N;

  sort `eneu_bylist';

  collapse (mean) eneu_lt105mw_ft_imss=eneu_lt105mw eneu_lt110mw_ft_imss=eneu_lt110mw
     rsal7_eneu_ft_imss_mn=rsal7 n_eneu_ft_imss age_mn_eneu=age age_cat2_eneu_*
       firmsize3_eneu_*
    (median) rsal7_eneu_ft_imss_med=rsal7 age_med_eneu=age
    (sum) empl_eneu_ft_imss = eneu  [pw=factor],
    by(`eneu_bylist');

  sort `eneu_bylist';

  tempfile d1;
  save `d1', replace;
  restore;

  ** full-time or part-time with imss;

  preserve;
  keep if work_imss==1;

  gen n_eneu_imss = _N;

  sort `eneu_bylist';

  collapse (mean) eneu_lt105mw_imss=eneu_lt105mw eneu_lt110mw_imss=eneu_lt110mw
    rsal7_eneu_imss_mn=rsal7 n_eneu_imss age_mn_eneu=age age_cat2_eneu_*
      firmsize3_eneu_*
    (median) rsal7_eneu_imss_med=rsal7 age_med_eneu=age
    (sum) empl_eneu_imss = eneu  [pw=factor],
    by(`eneu_bylist');

  sort `eneu_bylist';

  tempfile d2;
  save `d2', replace;
  restore;

  ** full-time, no imss;

  preserve;
  keep if work_ft==1 & work_ft_imss==0;

  gen n_eneu_ft_noimss = _N;

  sort `eneu_bylist';

  collapse (mean) eneu_lt105mw_ft_noimss = eneu_lt105mw eneu_lt110mw_ft_noimss = eneu_lt110mw
    rsal7_eneu_mn_ft_noimss=rsal7 n_eneu_ft_noimss age_mn_eneu=age age_cat2_eneu_*
      firmsize3_eneu_*
    (median) rsal7_eneu_med_ft_noimss=rsal7 age_med_eneu=age
    (sum) empl_eneu_ft_noimss = eneu [pw=factor],
    by(`eneu_bylist');

  sort `eneu_bylist';

  tempfile d3;
  save `d3', replace;
  restore;

  ** full-time or part-time, no imss;

  preserve;
  keep if work_all==1 & work_imss==0;

  gen n_eneu_noimss = _N;

  sort `eneu_bylist';

  collapse (mean) eneu_lt105mw_noimss = eneu_lt105mw eneu_lt110mw_noimss = eneu_lt110mw
    rsal7_eneu_mn_noimss=rsal7 n_eneu_noimss age_mn_eneu=age age_cat2_eneu_*
      firmsize3_eneu_*
    (median) rsal7_eneu_med_noimss=rsal7 age_med_eneu=age
    (sum) empl_eneu_noimss = eneu [pw=factor],
    by(`eneu_bylist');

  sort `eneu_bylist';

  tempfile d4;
  save `d4', replace;
  restore;

  ** written contract of indefinite duration, imss;

  preserve;
  keep if contract_type==10 & work_imss==1;

  gen n_eneu_perm_imss = _N;

  sort `eneu_bylist';

  collapse (mean) eneu_lt105mw_perm_imss = eneu_lt105mw
    eneu_lt110mw_perm_imss = eneu_lt110mw
    rsal7_eneu_mn_perm_imss=rsal7 n_eneu_perm_imss age_mn_eneu=age age_cat2_eneu_*
      firmsize3_eneu_*
    (median) rsal7_eneu_med_perm_imss=rsal7 age_med_eneu=age
    (sum) empl_eneu_perm_imss = eneu [pw=factor],
    by(`eneu_bylist');

  tab year;

  sort `eneu_bylist';

  tempfile d5;
  save `d5', replace;
  restore;


  ** written contract of indefinite duration, no imss;

  preserve;
  keep if contract_type==10 & work_imss==0;

  gen n_eneu_perm_noimss = _N;

  sort `eneu_bylist';

  collapse (mean) eneu_lt105mw_perm_noimss = eneu_lt105mw
    eneu_lt110mw_perm_noimss = eneu_lt110mw
    rsal7_eneu_mn_perm_noimss=rsal7 n_eneu_perm_noimss age_mn_eneu=age age_cat2_eneu_*
      firmsize3_eneu_*
    (median) rsal7_eneu_med_perm_noimss=rsal7 age_med_eneu=age
    (sum) empl_eneu_perm_noimss = eneu [pw=factor],
    by(`eneu_bylist');

  tab year;

  sort `eneu_bylist';

  tempfile d6;
  save `d6', replace;
  restore;

  ** merge ENEU files;

  clear;
  use `d1';
  merge 1:1 `eneu_bylist' using `d2';
  drop _merge;
  merge 1:1 `eneu_bylist' using `d3';
  drop _merge;
  merge 1:1 `eneu_bylist' using `d4';
  drop _merge;
  merge 1:1 `eneu_bylist' using `d5';
  drop _merge;
  merge 1:1 `eneu_bylist' using `d6';
  drop _merge;

  save ${tmp}eneu_`filename', replace;


  *********** Merge IMSS & ENEU *************;

  clear;
  use ${tmp}imss_`filename';
  sort `eneu_bylist';

  merge m:1 `eneu_bylist' using ${tmp}eneu_`filename';
  drop _merge;

  save ${work}`filename', replace;

 };



log close;

